package org.sction.manager.file;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.TreeMap;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.sction.bean.ExcelCellBean;


public class ToExcelUtils {
	public static Workbook createWorkbook(boolean xlsx,
			TreeMap<String, List<List<ExcelCellBean>>> data, ToExcelRender ter) {
		Workbook wb = (Workbook) (xlsx ? new XSSFWorkbook()
				: new HSSFWorkbook());
		TreeMap<String, CellStyle> styles = ter.renderStyles(wb);
		Iterator<String> it = data.keySet().iterator();
		while (it.hasNext()) {
			String sheetName = it.next();
			Sheet sheet = wb.createSheet(sheetName);
			writeSheet(sheet, data.get(sheetName), styles);
		}
		return wb;
	}

	private static void writeSheet(Sheet sheet,
			List<List<ExcelCellBean>> rowdatas,
			TreeMap<String, CellStyle> styles) {
		for (int i = 0; i < rowdatas.size(); i++) {
			List<ExcelCellBean> rowdata = rowdatas.get(i);
			int rownum = 0;
			int cellnum = 0;
			for (int j = 0; j < rowdata.size(); j++) {
				ExcelCellBean celldata = rowdata.get(j);
				rownum = celldata.getRow();
				cellnum = celldata.getCell();
				Row row = sheet.getRow(rownum);
				if (row == null) {
					row = sheet.createRow(rownum);
				}
				if (celldata.getRowHeight() != -1) {
					row.setHeightInPoints(celldata.getRowHeight());
				}
				Cell cell = row.createCell(cellnum);
				cell.setCellStyle(styles.get(celldata.getClassStyle()));
				if (celldata.getWidth() > 0) {
					sheet.setColumnWidth(cellnum,
							(int) (celldata.getWidth() * 35.7));
				}
				if (celldata.getHeight() > 0) {
					sheet.setDefaultRowHeight((short) (celldata.getHeight() * 35.7));
				}
				if (celldata.getRows() > 1 || celldata.getCells() > 1) {
					for (int k = 0; k < celldata.getRows(); k++) {
						Row rowtemp = sheet.getRow(rownum + k);
						if (rowtemp == null) {
							rowtemp = sheet.createRow(rownum + k);
						}
						for (int k1 = 0; k1 < celldata.getCells(); k1++) {
							Cell celltemp = sheet.getRow(celldata.getRow() + k)
									.getCell(celldata.getCell() + k1);
							if (celltemp == null) {
								celltemp = sheet.getRow(celldata.getRow() + k)
										.createCell(celldata.getCell() + k1);
							}
							celltemp.setCellStyle(styles.get(celldata
									.getClassStyle()));
						}
					}
					// System.out.println("" + celldata.getRow() + ":"
					// + (celldata.getRow() + celldata.getRows() - 1)
					// + ":" + celldata.getCell() + ":"
					// + (celldata.getCell() + celldata.getCells() - 1));
					sheet.addMergedRegion(new CellRangeAddress(celldata
							.getRow(), celldata.getRow() + celldata.getRows()
							- 1, celldata.getCell(), celldata.getCell()
							+ celldata.getCells() - 1));
				}
				Object d = celldata.getData();
				if (celldata.isFormula()) {
					cell.setCellFormula(d.toString());
				} else {
					if (d instanceof Integer) {
						cell.setCellValue(Integer.parseInt(d.toString()));
					} else if (d instanceof Double) {
						cell.setCellValue(Double.parseDouble(d.toString()));
					} else if (d instanceof Float) {
						cell.setCellValue(Float.parseFloat(d.toString()));
					} else if (d instanceof Date) {
						cell.setCellValue((Date) d);
					} else if (d instanceof Boolean) {
						cell.setCellValue((Boolean) d);
					} else {
						if (d != null && !d.toString().equals("")) {
							cell.setCellValue(d.toString());
						}
					}

				}
			}
		}
	}

	public static void main(String[] args) {
		TreeMap<String, List<List<ExcelCellBean>>> data = new TreeMap<String, List<List<ExcelCellBean>>>();
		List<List<ExcelCellBean>> sheetdata = new ArrayList<List<ExcelCellBean>>();

		List<ExcelCellBean> rowdata = new ArrayList<ExcelCellBean>();
		ExcelCellBean title1 = new ExcelCellBean();
		title1.setCells(2);
		title1.setRows(2);
		title1.setData("测试报表头");

		rowdata.add(title1);

		ExcelCellBean title2 = new ExcelCellBean();
		title2.setCells(2);
		title2.setCell(2);
		title2.setRow(0);
		title2.setData("2");
		rowdata.add(title2);
		sheetdata.add(rowdata);

		List<ExcelCellBean> rowdata2 = new ArrayList<ExcelCellBean>();
		ExcelCellBean title4 = new ExcelCellBean();
		title4.setCell(2);
		title4.setRow(1);
		title4.setData("4");
		rowdata2.add(title4);
		ExcelCellBean title5 = new ExcelCellBean();
		title5.setCell(3);
		title5.setRow(1);
		title5.setData("5");
		rowdata2.add(title5);
		sheetdata.add(rowdata2);

		List<ExcelCellBean> rowdata3 = new ArrayList<ExcelCellBean>();
		ExcelCellBean title6 = new ExcelCellBean();
		title6.setCell(0);
		title6.setRow(2);
		title6.setData(6);
		rowdata3.add(title6);
		ExcelCellBean title7 = new ExcelCellBean();
		title7.setCell(1);
		title7.setRow(2);
		title7.setData(7);
		rowdata3.add(title7);
		ExcelCellBean title8 = new ExcelCellBean();
		title8.setCell(2);
		title8.setRow(2);
		title8.setData(8);
		rowdata3.add(title8);
		ExcelCellBean title9 = new ExcelCellBean();
		title9.setCell(3);
		title9.setRow(2);
		title9.setFormula(true);
		title9.setData("SUM(A3:C3)");
		rowdata3.add(title9);
		sheetdata.add(rowdata3);

		data.put("测试工作薄", sheetdata);
		OutputStream out;
		try {
			out = new FileOutputStream("D:\\" + new Date().getTime() + ".xlsx");

			Workbook wb = ToExcelUtils.createWorkbook(true, data,
					new SimpleToExcelRender());
			wb.write(out);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}
}
